import argparse
import logging
import os
import re
import sys

# 获取当前文件的绝对路径
current_dir = os.path.dirname(os.path.abspath(__file__))
# 将当前目录添加到sys.path中
if current_dir not in sys.path:
    sys.path.insert(0, current_dir)

from mysql_tool import task_config
from sql_operation import SqlPipelineBuilder, SqlCheck, SqlTransform

## 校验规则配置
verify_config = {
    'v_1': {'flag': True, 'msg': '[select *]关键字校验'},  # select
    'v_2': {'flag': True, 'msg': '[length()>0]关键字校验'},  # length()>0
    'v_3': {'flag': True, 'msg': '[row_number()over(partition by)]关键字校验'},  # row_number()over(partition by)
    'v_4': {'flag': True, 'msg': '[ifnull]关键字校验'},  # ifnull函数
    'v_5': {'flag': True, 'msg': '[truncate]关键字校验'},  # truncate函数
    'v_6': {'flag': True, 'msg': '[|| / &&]关键字校验'},  # || / &&
    'v_7': {'flag': True, 'msg': '[group_concat]关键字校验'},  # group_concat
    'v_8': {'flag': True, 'msg': '[->> ->]关键字校验'},  # ->> ->
    'v_9': {'flag': True, 'msg': '[date_add/date_sub]关键字校验'},  # date_add/date_sub
    'v_10': {'flag': True, 'msg': '[/* */]关键字校验'}  # /* */
}
## 转换规则配置
convert_config = {
    'c_1': True,  # 预处理+distribute by hospital_code
    'c_2': True,  # 时间格式
    'c_3': True,  # 批次号
    # 'c_4': True,  #
    'c_4': True,  # date_sub/date_add
    'c_5': True,  # length
    'c_6': True,  # group_concat
    'c_7': True,  # json处理
    'c_8': True  # str_to_date => date_format
}
## db config
db_config = {
    'host': '',
    'port': '4000',
    'user': 'root',
    'password': '1hbl3qt!',
    'db': 'di_vision',
    'charset': 'utf8mb4',
}
## db_name_config
db_name_config = {
    "wotu_std": '',
    "wotu_mid": '',
    "wotu_cdm": '',
    "wotu_dim": '',
    'di_dim': '',
    'batch_no': '',
    'task_name': '',
    'hospital_code_list': '',
    'cancer_species':''
}


## 关闭规则列表
def str_list(value):
    return value.split(",")


def main():
    ## 日志配置
    logger = logging.getLogger(__name__)
    logger.setLevel(logging.DEBUG)
    ch = logging.StreamHandler()
    ch.setLevel(logging.DEBUG)
    formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
    ch.setFormatter(formatter)  # 设置控制台handler的输出格式
    logger.addHandler(ch)
    # 解析器参数
    parser = argparse.ArgumentParser()
    ## 添加参数
    parser.add_argument("-v", "--verify", help="校验", required=False, action="store_true")
    parser.add_argument("-c", "--convert", help="转换", required=False, action="store_true")
    parser.add_argument("-vr", "--verify_rule", type=str_list, help="校验规则", required=False)
    parser.add_argument("-cr", "--convert_rule", type=str_list, help="转换规则", required=False)
    ## sql 固定替换
    parser.add_argument("--wotu_std", help="std", required=True)
    parser.add_argument("--wotu_mid", help="mid", required=True)
    parser.add_argument("--wotu_cdm", help="cdm", required=True)
    parser.add_argument("--wotu_dim", help="wotu_dim", required=True)
    parser.add_argument("--di_dim", help="di_dim", required=True)
    parser.add_argument("--batch_no", help="批次号", required=True)
    parser.add_argument("--task_name", help="任务名称", required=True)
    parser.add_argument("--hospital_code_list", help="医院集合", required=True)

    ## 数据连接信息
    parser.add_argument("--host", help="数据库ip", required=True)
    parser.add_argument("--user", help="数据库user", required=False)
    parser.add_argument("--password", help="数据库password", required=False)
    parser.add_argument("--db", help="数据库库名", required=False)
    parser.add_argument("--port", help="数据库端口", required=False)

    # 解析参数
    args = parser.parse_args()
    print("程序入参{}".format(args))
    ## 使用参数
    if args.verify_rule:
        print("关闭指定校验规则")
        for key in args.verify_rule:
            if key in verify_config:
                verify_config[key]['flag'] = False
    if args.convert_rule:
        print("关闭指定转换规则")
        for key in args.convert_rule:
            if key in convert_config:
                convert_config[key] = False
    ## sql 固定替换
    if args.wotu_std:
        db_name_config['wotu_std'] = args.wotu_std
    if args.wotu_mid:
        db_name_config['wotu_mid'] = args.wotu_mid
    if args.wotu_cdm:
        db_name_config['wotu_cdm'] = args.wotu_cdm
    if args.wotu_dim:
        db_name_config['wotu_dim'] = args.wotu_dim
    if args.di_dim:
        db_name_config['di_dim'] = args.di_dim
    if args.batch_no:
        db_name_config['batch_no'] = args.batch_no
    if args.task_name:
        db_name_config['task_name'] = args.task_name
    if args.hospital_code_list:
        db_name_config['hospital_code_list'] = args.hospital_code_list
    if args.cancer_species:
        db_name_config['cancer_species'] = args.cancer_species
    ### 数据连接信息
    if args.host:
        db_config['host'] = args.host
    if args.user:
        db_config['user'] = args.user
    if args.password:
        db_config['password'] = args.password
    if args.db:
        db_config['db'] = args.db
    if args.port:
        db_config['port'] = args.port

    ### sql 文本
    ## sql = task_config(db_config=db_config, task_name=args.task_name)
    #
    sql = """select group_concat(a separator ','),group_concat(a separator ',' order by a desc) from table1;"""
    # sql = """select a.* from table1;
    #         select a from table1 where length(a)>0;
    #         select row_number() over (partition by a) from table1;
    #         select ifnull(a) from table1;
    #         select truncate(a) from table2;
    #         select a from table1 where a is null || a ='' && a>1;
    #         select group_concat(a) from table1;select group_concat(a separator ',' order by a,b desc);
    #         select a ->> c from table1;
    #         select date_add(a,interval 1 day) from table1;select date_sub(a,interval 1 day) from table1;
    #         /* */
    # """
    print("原始sql {}".format(sql))

    # 构建操作链
    builder = SqlPipelineBuilder()
    verify_method(args, builder, logger)
    convert_method(args, builder, logger)
    ## 构建执行管道
    pipeline = builder.build()
    transformed_sql, failed_checks = pipeline.execute(sql)
    if failed_checks:
        print(f"SQL: '{sql}' failed checks: {', '.join(failed_checks)}")
    else:
        print(f"Processed SQL: {transformed_sql}")

    with open(args.task_name, 'a') as file:
        file.write(transformed_sql)


def convert_method(args, builder, logger):
    if args.convert:
        print("转换开启")
        if convert_config['c_2']:  ## 时间格式替换
            builder.add_operation(SqlTransform(rule_name="c_2", regex_pattern=r'%(Y|y)-%m-%d %(H|h):%i:%(S|s)',
                                               replacement="yyyy-MM-dd HH:mm:ss", logger=logger))
        if convert_config['c_3']:  ## 添加批次号
            builder.add_operation(SqlTransform(rule_name="c_3", regex_pattern='\$\{sys\!exec_data_batch_version\}',
                                               replacement=db_name_config['batch_no'], logger=logger))
        if convert_config['c_4']:  ## date_sub/date_add
            builder.add_operation(SqlTransform(rule_name="c_4", reflect=True,logger=logger))
        if convert_config['c_5']:  ## length 函数替换
            builder.add_operation(SqlTransform(rule_name="c_5", regex_pattern=r' length\(', replacement=" yy_length(", logger=logger))
        if convert_config['c_6']:  ## group_concat
            builder.add_operation(SqlTransform(rule_name="c_6", reflect=True, logger=logger))
        if convert_config['c_7']:  ## json_unquote(json_extract(doc,path_literal))
            builder.add_operation(SqlTransform(rule_name="c_7", reflect=True, logger=logger))
        if convert_config['c_8']:  ## str_to_date=>date_format
            builder.add_operation(SqlTransform(rule_name="c_8", regex_pattern=r' str_to_date\(', replacement=" date_format(",logger=logger))
        if convert_config['c_1']:
            builder.add_operation(SqlTransform(rule_name="c_1", reflect=True, logger=logger,db_name_config=db_name_config))
            # ●分割sql
            # ●删除on duplcate key 后面语句
            # ●删除delete/truncate/set 语句
            # ●提取库名、表名、sql语句
            # ●多个sql中，全部都为insert into
            # ●替换大禹治水变量
            # ●替换库名和表名: wotu_std. wotu_mid, wotu_cdm, di_dim, wotu_dim等名称，使⽤传递进来的名称
            # ●添加重分区


def verify_method(args, builder, logger):
    if args.verify:
        print("校验开启")
        if verify_config['v_1']['flag']:
            builder.add_operation(SqlCheck(rule_name="v_1", regex_pattern=r"select\s+(\w+\.)?\*",
                                           error_message=verify_config['v_1']['msg'], logger=logger))
        if verify_config['v_2']['flag']:
            builder.add_operation(SqlCheck(rule_name="v_2", regex_pattern=r"length\([^)]+\)>\s*0",
                                           error_message=verify_config['v_2']['msg'], logger=logger))
        if verify_config['v_3']['flag']:
            builder.add_operation(
                SqlCheck(rule_name="v_3", error_message=verify_config['v_3']['msg'], logger=logger, reflect=True))
        if verify_config['v_4']['flag']:
            builder.add_operation(
                SqlCheck(rule_name="v_4", regex_pattern=r"ifnull\((.*?)\)", error_message=verify_config['v_4']['msg'],
                         logger=logger))
        if verify_config['v_5']['flag']:
            builder.add_operation(
                SqlCheck(rule_name="v_5", regex_pattern=r"truncate\((.*?)\)", error_message=verify_config['v_5']['msg'],
                         logger=logger))
        if verify_config['v_6']['flag']:
            builder.add_operation(
                SqlCheck(rule_name="v_6", regex_pattern=r'\|\|', error_message=verify_config['v_6']['msg'],
                         logger=logger))
            builder.add_operation(
                SqlCheck(rule_name="v_6", regex_pattern=r'&&', error_message=verify_config['v_6']['msg'],
                         logger=logger))
        if verify_config['v_7']['flag']:
            builder.add_operation(
                SqlCheck(rule_name='v_7', error_message=verify_config['v_7']['msg'], logger=logger, reflect=True))
        if verify_config['v_8']['flag']:
            builder.add_operation(
                SqlCheck(rule_name="v_8", regex_pattern=r'\->(?:>>)?', error_message=verify_config['v_8']['msg'],
                         logger=logger))
        if verify_config['v_9']['flag']:
            builder.add_operation(
                SqlCheck(rule_name="v_9", error_message=verify_config['v_9']['msg'],
                         logger=logger, reflect=True))
        if verify_config['v_10']['flag']:
            builder.add_operation(
                SqlCheck(rule_name="v_10", regex_pattern=r'/\*|\*/', error_message=verify_config['v_10']['msg'],
                         logger=logger))


if __name__ == "__main__":
    main()
